CREATE TABLE userInfo 
(
  customerID  SERIAL PRIMARY KEY,
  customerName CHAR(8),
  PID CHAR(18),
  telephone CHAR(20),
  address VARCHAR(50)
);

CREATE TABLE cardInfo
(
  cardID  CHAR(17) PRIMARY KEY,
  curID  VARCHAR(10),
  savingID INT,
  openDate  TIMESTAMP,
  openMoney  NUMERIC(18,2),
  balance  NUMERIC(18,2),
  pass CHAR(6),
  IsReportLoss VARCHAR(3),
  customerID INT
);

CREATE TABLE tradeInfo
(
  tradeDate  TIMESTAMP,
  tradeType  CHAR(6),
  cardID  CHAR(19),
  tradeMoney  NUMERIC(18,2),
  remark  TEXT
);

CREATE TABLE deposit
(
  savingID  SERIAL PRIMARY KEY,
  savingName  VARCHAR(20) NOT NULL,
  descrip VARCHAR(50)
);

INSERT INTO deposit (savingName,descrip) VALUES ('活期','按存款日结算利息');
INSERT INTO deposit (savingName,descrip) VALUES ('定期一年','存款期是1年');
INSERT INTO deposit (savingName,descrip) VALUES ('定期二年','存款期是2年');
INSERT INTO deposit (savingName,descrip) VALUES ('定期三年','存款期是3年');
INSERT INTO deposit (savingName) VALUES ('定活两便');
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取一年','存款期是1年');
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取二年','存款期是2年');
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取三年','存款期是3年');


INSERT INTO userInfo(customerid,customerName,PID,telephone,address)
     VALUES(1,'John','110000000000000001','13900000001','江苏省南京市');
INSERT INTO userInfo(customerid,customerName,PID,telephone)
     VALUES(2,'Jane','110000000000000002','13900000002');
INSERT INTO userInfo(customerid,customerName,PID,telephone)
     VALUES(3,'Michael','110000000000000003','13900000003');
INSERT INTO userInfo(customerid,customerName,PID,telephone)
     VALUES(4,'Emily','110000000000000004','13900000004');
INSERT INTO userInfo(customerid,customerName,PID,telephone)
     VALUES(5,'David','110000000000000005','13900000005');
INSERT INTO userInfo(customerid,customerName,PID,telephone)
     VALUES(6,'Sarah','110000000000000006','13900000006');
INSERT INTO userInfo(customerid,customerName,PID,telephone)
     VALUES(7,'Robert','110000000000000007','13900000007');
INSERT INTO userInfo(customerid,customerName,PID,telephone)
     VALUES(8,'Jessica','110000000000000008','13900000008');
INSERT INTO userInfo(customerid,customerName,PID,telephone)
     VALUES(9,'William','110000000000000009','13900000009');
INSERT INTO userInfo(customerid,customerName,PID,telephone)
     VALUES(10,'Olivia','110000000000000010','13900000010');

INSERT INTO cardInfo(cardid,curid,savingid,openmoney,balance,pass,isreportloss,customerid)
     VALUES('1010357600000001','RMB',1,1000.00,1000.00,'888888','否',1);
INSERT INTO cardInfo(cardid,curid,savingid,openmoney,balance,pass,isreportloss,customerid)
     VALUES('1010357600000002','RMB',2,1.00,1.00,'888888','否',2);
INSERT INTO cardInfo(cardid,curid,savingid,openmoney,balance,pass,isreportloss,customerid)
     VALUES('1010357600000003','RMB',2,1.00,1.00,'888888','否',3);
INSERT INTO cardInfo(cardid,curid,savingid,openmoney,balance,pass,isreportloss,customerid)
     VALUES('1010357600000004','RMB',2,1.00,1.00,'888888','否',4);
INSERT INTO cardInfo(cardid,curid,savingid,openmoney,balance,pass,isreportloss,customerid)
     VALUES('1010357600000005','RMB',3,500.00,500.00,'888888','否',5);
INSERT INTO cardInfo(cardid,curid,savingid,openmoney,balance,pass,isreportloss,customerid)
     VALUES('1010357600000006','RMB',3,300.00,300.00,'888888','否',6);
INSERT INTO cardInfo(cardid,curid,savingid,openmoney,balance,pass,isreportloss,customerid)
     VALUES('1010357600000007','RMB',3,200.00,200.00,'888888','否',7);
INSERT INTO cardInfo(cardid,curid,savingid,openmoney,balance,pass,isreportloss,customerid)
     VALUES('1010357600000008','RMB',3,150.00,150.00,'888888','否',8);
INSERT INTO cardInfo(cardid,curid,savingid,openmoney,balance,pass,isreportloss,customerid)
     VALUES('1010357600000009','RMB',3,100.00,100.00,'888888','否',9);
INSERT INTO cardInfo(cardid,curid,savingid,openmoney,balance,pass,isreportloss,customerid)
     VALUES('1010357600000010','RMB',3,80.00,80.00,'888888','否',10);


CREATE VIEW v_user_card_info AS
SELECT
    u.customerID,
    u.customerName,
    u.PID,
    u.telephone,
    u.address,
    c.cardID,
    c.curID,
    c.savingID,
    d.savingName,
    c.openDate,
    c.openMoney,
    c.balance,
    c.IsReportLoss
FROM userInfo u
LEFT JOIN cardInfo c ON u.customerID = c.customerID
LEFT JOIN deposit d ON c.savingID = d.savingID;


CREATE OR REPLACE FUNCTION auto_open_card()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO cardInfo(cardID, curID, savingID, openDate, openMoney, balance, pass, IsReportLoss, customerID)
    VALUES (
        -- 生成卡号，可用序列或拼接
        nextval('card_id_seq')::text, -- 你需要先创建一个序列
        'RMB',
        1,
        NOW(),
        0.00,
        0.00,
        '888888',
        '否',
        NEW.customerID
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auto_open_card
AFTER INSERT ON userInfo
FOR EACH ROW
EXECUTE FUNCTION auto_open_card();


CREATE OR REPLACE PROCEDURE transfer_money(
    from_card CHAR(17),
    to_card CHAR(17),
    amount NUMERIC(18,2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 检查余额
    IF (SELECT balance FROM cardInfo WHERE cardID = from_card AND IsReportLoss = '否') < amount THEN
        RAISE EXCEPTION '余额不足或卡已挂失';
    END IF;

    -- 扣减转出卡
    UPDATE cardInfo SET balance = balance - amount
    WHERE cardID = from_card AND IsReportLoss = '否';

    -- 增加转入卡
    UPDATE cardInfo SET balance = balance + amount
    WHERE cardID = to_card AND IsReportLoss = '否';

    -- 记录交易（可调用触发器自动完成，见第4点）
END;
$$;





